-- extended event session to find inaccurate cardinality estimates
IF EXISTS(SELECT 1 FROM sys.server_event_sessions WHERE name = N'CardinalityIssues')
	DROP EVENT SESSION CardinalityIssues ON SERVER;
GO

-- create event session
CREATE EVENT SESSION CardinalityIssues ON SERVER 
ADD EVENT sqlserver.inaccurate_cardinality_estimate(
	ACTION
	(
		sqlserver.database_id,
		sqlserver.database_name,
		sqlserver.plan_handle,
		sqlserver.sql_text
	)
	WHERE (sqlserver.is_system = 0 AND sqlserver.database_id > 4)
)
ADD TARGET package0.event_file
(
	SET FILENAME = N'C:\Temp\CardinalityIssues.xel',
		MAX_FILE_SIZE = 200, MAX_ROLLOVER_FILES = 5
);
GO

-- start the session
ALTER EVENT SESSION CardinalityIssues ON SERVER STATE = START;

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION CardinalityIssues ON SERVER STATE = STOP;
GO

-- query target data
SELECT 
	--event_data.value('(@name)[1]','varchar(50)') AS event_name, 
	DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),n.value('(@timestamp)[1]','datetime2')) AS 'timestamp',
	n.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id,
    n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS database_name,
	n.value('(data[@name="estimated_rows"]/value)[1]','int') AS estimated_rows, 
	n.value('(data[@name="actual_rows"]/value)[1]','int') AS actual_rows, 
	n.value('(data[@name="node_id"]/value)[1]','int') AS node_id, 
	n.value('(data[@name="fire_count"]/value)[1]','int') AS fire_count, 
	n.value('xs:hexBinary((action[@name="plan_handle"]/value)[1])','varbinary(64)') AS plan_handle,
	n.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') AS sql_text
FROM (
	SELECT
		CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file
	-- SQL 2008 (R2)
	-- (N'C:\Temp\CardinalityIssues*.xel', N'C:\Temp\CardinalityIssues*.xem', NULL, NULL)
	-- SQL 2012 no metadata file required
	(N'C:\Temp\CardinalityIssues*xel', NULL, NULL, NULL)
) AS tab 
CROSS APPLY event_data.nodes('//event') AS q(n);
GO

-- check the query plan
SELECT
	query_plan
FROM sys.dm_exec_query_plan (<plan_handle>);

-- cleanup
DROP EVENT SESSION CardinalityIssues ON SERVER;
GO